Mega Code Archive
Query Select
1) (1=1 or 1=0) and 0=1 (case 2)
2) (state = CA) OR (state CA)
3) 1=1 or (1=0 and 0=1) (case 3)
4) 1=1 or 1=0 and 0=1 (case 1)
5) A correlated subquery
6) A Couple of Errors when using subqueries in a FROM Clause (Inline Views)
7) A query that returns a result based on other rows
8) A SELECT with Just a FROM Clause
9) A SELECT with Ordering
10) A WHERE Clause Is Added to the Statement
11) Add aggregate function in order by clause
12) Add where clause to a Hierarchical Query
13) Adds an ORDER BY clause
14) An alias is required when using TO_CHAR to pretty up the output
15) Any row-level variable (i e , a column name) in the result set must be mentioned in the GROUP BY clause for the query to make
16) Born after 1960-01-01, group by department number with count() = 4
17) By default, GROUP BY sorts the rows in ascending order
18) Column for alias
19) Column name alias for count function
20) Column name alias with space
21) Combine conditions with AND and OR
22) Combine more than one operator in an expression
23) Combine NOT and AND
24) Combine order by clause with case statement
25) Combine subquery and other conditions
26) Combine three conditions with OR
27) Combine UPPER and LIKE operator
28) Combined columns and renaming
29) Compare Number type in where clause
30) Compare with number
31) Concatenate string
32) CONDITION
33) CONNECT BY NOCYCLE PRIOR
34) Convert char to number implicit in where clause
35) Correlated subquery using the EXISTS operator
36) Count and group by department id
37) Count distinct with group by
38) Count employee, group by department id and job title
39) Count() and column renaming
40) Deal with NULL with NOT IN and NVL
41) Demo record-based DML and Bulk Collect
42) Demo record-based DML using PLSQL table and FORALL
43) Demonstrate the 9i R2 record-based DML support
44) DISTINCT tableName
45) Display department number and its employee count if it has more than 4 employees
46) Displaying Distinct Rows
47) Do calculation in where clause
48) Dont Perform the Same Calculation Over and Over
49) Earn more money than all managers
50) Earn more than any managers
51) Earn more than average
52) Eliminating Nodes and Branches from a Hierarchical Query
53) Employee level
54) Employee tree
55) ESCAPE from LIKE
56) ESCAPE option
57) EXISTS and NOT EXISTS Versus IN and NOT IN
58) Exist with subquery
59) Exists in subquery
60) Expression and column renaming
61) Extract a Subset of the Results
62) Filtering Rows Using the WHERE Clause
63) Find all employees who are younger than employee whose id is 9999
64) First three rows from subquery
65) Format date value and column renaming
66) Formatting the Results from a Hierarchical Query
67) Get all managers
68) Getting the Five Most Expensive Products
69) Getting Values and Subtotals in One Go
70) Getting Values and Subtotals in One Go with COALESCE
71) Getting Values and Subtotals in One Go with UNION
72) Greater than all subquery
73) Greater than any subquery
74) Group all ids with averager value more than
75) Group and count employeem and display only if its count is more than 4
76) Group by case
77) GROUP BY used on a column without the column name appearing in the result set
78) GROUP by with NULL value
79) Grouping at Multiple Levels
80) Hierarchical Queries
81) How Many Products By Department with correlated subqueries 1
82) How Many Products By Department with correlated subqueries 2
83) have the subquery select from a different table than the parent statement
84) If the value is not null
85) If value is null or less than a value
86) In and subquery
87) In subquery
88) In what city do we have the most employees
89) Inline view
90) Intersect rowid
91) IS NULL clause
92) Join a subquery
93) List all employees who are younger than Jane
94) List all employees who attended XML course and Java course
95) List all employees who is younger than Joe
96) List all non-manager employee
97) List department who has more than 4 employee born after 1960
98) List single column from a table
99) List the employees whose salary is higher than the average salary with self join
100) Map pair with subquery by using IN operator
101) Math calculation in select statement
102) Merging Column Output Using Concatenation
103) Multi-column subquery
104) Multi-row subqueries
105) Negate a column value
106) NO_INDEX function in select statement
107) NOT BETWEEN
108) Not empty(null)
109) Not Equal and OR
110) Not Equal subquery
111) Not equals
112) Not equals and subquery
113) Not Exist with subquery
114) Not exists and subquery
115) Not has the lowerest priority
116) NOT IN and subquery
117) NOT IN returns false if a value in the list is null
118) NOT IN reverses the rows selected by IN
119) Not IN subquery
120) Not in (1,2,3,NULL)
121) Not in vs not exists
122) NOT operator has higher priority than AND
123) NOT to negate a boolean value
124) Not(condition1 and condition2)
125) Older than another employee
126) Or, and, not
127) ORA-00979
128) Order by a calculated column
129) Order by two columns
130) Order by username
131) Order the table on the employees original salary (orig_salary)
132) Order Your Email
133) Output full path of hierarchy
134) Percent character (%) matches any number of characters beginning at the specified position
135) Performing Arithmetic
136) Performing Range Tests, not between and
137) Performing Single Table SELECT Statements
138) Pseudocolumn LEVEL and an example of using the levels
139) Pseudocolumn LEVEL and an example of using the levels with an update
140) Reference alias name in order by clause
141) Reference one column more than once
142) Reference renamed column in order by
143) Rename case when column
144) Replace IN operator with or operator
145) Return null from subquery
146) Returning Multiple Columns
147) Search for String Across Columns
148) Second letter is A
149) Select constant as a column
150) Select distinct column and other columns
151) Select distinct value
152) Select employee first and last and sort by last name
153) Select from a Subquery
154) Select from another select statement
155) Select with rownum
156) Selecting All Columns from a Table with asterisk character ()
157) SELECTs using the ORDER BY clause with rownum
158) Show products that are more expensive than average
159) Sort by index ascending and descending
160) Sort last name ascending
161) Sort last name descending
162) Sort one column ascending and another column descending
163) Sort the rows by descending order with DESC appended to order by clause
164) Sort two columns with different ordering
165) Sorting Rows Using the ORDER BY Clause
166) START WITH
167) Start with job = MANAGER and connect by prior
168) Starting at a node other than the root
169) Subqueries in a FROM Clause (Inline Views)
170) Subqueries in a HAVING Clause
171) Subqueries in the WHERE Clause
172) Subqueries May Not Contain an ORDER BY Clause
173) Subqueries That Return Multiple Results
174) Subquery and update statement
175) Subquery as a column
176) Subquery in from clause
177) Subquery NOT IN
178) Subquery with comparison operator
179) Subquery with Like operator
180) SUBSTR with column alias
181) Sum alias
182) Sys_connect_by_path
183) SYS_GUID
184) Table renaming in from clause
185) The following example illustrates the use of a searched CASE expression
186) The following example illustrates the use of a simple CASE expression
187) The subquery returning the literal value 1
188) Traversing Upward Through the Tree
189) Underscore character (_) matches one character in a specified position
190) Understanding Row Identifiers (rowid)
191) Update price of products that arent selling
192) Use _ and % together
193) Use _ to match a phone number
194) Use a column position number in the ORDER BY clause
195) Use AND to link two conditions
196) Use as to specify the alias name
197) Use BETWEEN for CHAR or VARCHAR type values
198) Use BETWEEN for Date type values
199) Use CASE statement to deal with NULL
200) Use Case to output null value
201) Use case when and grouping function together
202) Use case when clause to decode value
203) Use case when statement with between and
204) Use case when statement with exists and subquery
205) Use case when statement with in()
206) Use case when statement with to_char() like
207) Use case when with comparasion operator
208) Use function in select clause
209) Use greater than in sub query( list all employees who is younger than No 7)
210) Use Having
211) Use IS INFINITE operator
212) Use IS NAN operator
213) Use IS NOT INFINITE operator
214) Use IS NOT NAN operator
215) Use in with subquery
216) Use logical operators in a searched CASE expression
217) Use more than one aggregate functions in a select statement
218) Use null value with IN operator
219) Use OR to link two conditions
220) Use parenthesis to change the order of AND and OR
221) Use rownum to limit the resultset
222) Use spaces and preserve the case of your alias text
223) Use table alias to reference column
224) Use table name to reference column name
225) Use the aggregate functions with the GROUP BY clause
226) Use the ALL operator in a WHERE clause to compare a value with all of the values in a list
227) Use the ANY operator in a WHERE clause to compare a value with any of the values in a list
228) Use the optional AS keyword before the alias
229) Use three _ together
230) Use varchar2 type value with IN operator
231) Uses NOT IN to check if an id is not in the list of id values in the employee table
232) Uses NOT LIKE
233) Uses the COUNT() function and LEVEL to get the number of levels in the tree
234) Using a Subquery in a START WITH Clause
235) Using ALL with a Multiple Row Subquery
236) Using ANY with a Multiple Row Subquery
237) Using Column Aliases
238) Using Columns in Arithmetic
239) Using EXISTS and NOT EXISTS with a Correlated Subquery
240) Using IN with a Multiple Row Subquery
241) Using Multiple Columns in a Group
242) Using NOT EXISTS with a Correlated Subquery
243) Using Not in and subquery
244) Using Pattern Matching LIKE ____ %
245) Using Searched CASE Expressions
246) Using Single Row Operators
247) Using the ALL Operator with subquery
248) Using the ANY Operator with subquery
249) Using the BETWEEN Operator
250) Using the CASE Expression
251) Using the CONNECT BY and START WITH Clauses
252) Using the EXISTS Operator with subquery
253) Using the HAVING Clause to Filter Groups of Rows
254) Using the IN Operator
255) Using the LEVEL Pseudo-Column
256) Using the LIKE Operator
257) Using the ORDER BY Clause to Sort Groups
258) Using the SQL Operators
259) Using the WHERE and GROUP BY Clauses Together
260) Using the WHERE, GROUP BY, and HAVING Clauses Together
261) Value pair and in operator
262) Where clause
263) Where comm = comm, compare with itself
264) Where null null
265) Where null=null (equals)
266) Where with complated boolean expressions
267) Where with count from subquery
268) Where with sub query
269) With clause and subquery
270) With statement and subquery
271) Wrap case when into sum() function
272) Writing Correlated Subqueries
273) Writing Multiple Column Subqueries
274) Writing Multiple Row Subqueries
275) Writing Nested Subqueries
276) Writing Single Row Subqueries
277) You dont have to include the columns used in the GROUP BY clause in your SELECT clause